﻿/*
   CustomerOrderTotals view
*/
if exists(select * from dbo.sysobjects where 
   id = object_id(N'[dbo].[vw_CustomerOrderTotals]') and
   OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_CustomerOrderTotals]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_CustomerOrderTotals]
AS
select
  s.CustomerId, 
  s.OrderId,
  s.TotalExtendedPrice,
  o.Freight,
  (s.TotalExtendedPrice + o.Freight) as OrderTotal   
	from [Order] o,
	(
	  select 
	   CustomerId, OrderId, 
       Sum(ExtendedPrice) as TotalExtendedPrice
	     from vw_customerOrdersProducts
	   group by CustomerId, OrderId
	) s

	where o.Id = s.OrderId
GO
